﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Text;
using Portal.Environment;

namespace Portal.CMS
{
    public class GroupLinkBLL
    {
        public GroupLinkCls CreateModel(string GroupLinkId)
        {
            return CreateModel(Environments.ConnectionString,Environments.ActiveLanguage, GroupLinkId);
        }

        public GroupLinkCls CreateModel(string ConnectionString,string ActiveLanguage,string GroupLinkId)
        {
            string Query = "select * from TableGroupLink where GroupLinkId=@GroupLinkId";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupLinkId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupLinkId"].Value = GroupLinkId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                GroupLinkCls GroupLinkCls = new GroupLinkCls();
                GroupLinkCls.GroupLinkId = (string)dtResult.Rows[0]["GroupLinkId"];
                try
                {
                    GroupLinkCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "Subject"];
                }
                catch { }
                GroupLinkCls.Owner = (string)dtResult.Rows[0]["Owner"];
                GroupLinkCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                return GroupLinkCls;
            }
        }

        public DataTable ReadingForSyncFromTool(string ConnectionString, string belongSite)
        {
            string Query = "select GroupLinkId from TableGroupLink where belongSite='" + belongSite + "'";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable Reading(string belongSite)
        {
            string Query = "select * from TableGroupLink where belongSite='" + belongSite+"'";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingForService()
        {
            string Query = "select Id=GroupLinkId,Subject from TableGroupLink order by GroupLinkId";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingGroupLinkItems(string GroupLinkId)
        {
            string Query = "select * from TableGroupLinkItem where fk_GroupLink_Id='" + GroupLinkId+"' order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string AddGroupLink(
            SqlTransaction sqlTransaction, 
            string belongSite,
            string owner,
            string Subject,
            GroupLinkItemCls[] GroupLinkItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string GroupLinkId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableGroupLink(GroupLinkId,belongSite,Subject,[" + Environments.ActiveLanguage + "Subject], owner) values (@GroupLinkId,@belongSite,@Subject,@Subject, @owner)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            sqlCommand.Parameters.Add("@GroupLinkId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupLinkId"].Value = GroupLinkId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;


            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@owner", SqlDbType.NVarChar);
            sqlCommand.Parameters["@owner"].Value = owner;


            try
            {
                sqlCommand.ExecuteNonQuery();
                

                for (int iIndex = 0; iIndex < GroupLinkItems.Length; iIndex++)
                {
                    if (GroupLinkItems[iIndex].GroupLinkItemName != null)
                    {
                        if (GroupLinkItems[iIndex].GroupLinkItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableGroupLinkItem(GroupLinkItemId,belongSite,GroupLinkItemName,[" + Environments.ActiveLanguage + "GroupLinkItemName], Url,Target,IndexOrder,fk_GroupLink_Id,["+Environments.ActiveLanguage+"Description]) values (@GroupLinkItemId,@belongSite,@GroupLinkItemName,@GroupLinkItemName, @Url,@Target,@IndexOrder,@fk_GroupLink_Id,@Description)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();

                            string GroupLinkItemId = System.Guid.NewGuid().ToString();

                            sqlCommand.Parameters.Add("@GroupLinkItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@GroupLinkItemId"].Value = GroupLinkItemId;

                            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@belongSite"].Value = belongSite;


                            sqlCommand.Parameters.Add("@GroupLinkItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@GroupLinkItemName"].Value = GroupLinkItems[iIndex].GroupLinkItemName;

                            sqlCommand.Parameters.Add("@Url", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Url"].Value = GroupLinkItems[iIndex].Url;

                            sqlCommand.Parameters.Add("@Target", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Target"].Value = GroupLinkItems[iIndex].Target;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = GroupLinkItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_GroupLink_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_GroupLink_Id"].Value = GroupLinkId;

                            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Description"].Value =  GroupLinkItems[iIndex].Description;


                            sqlCommand.ExecuteNonQuery();
                        }
                    }
                }
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return GroupLinkId;
        }



        public void UpdateGroupLink(SqlTransaction sqlTransaction,string Language, string GroupLinkId, string Subject,GroupLinkItemCls[] GroupLinkItems)
        {
            GroupLinkCls GroupLinkCls = new GroupLinkBLL().CreateModel(GroupLinkId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableGroupLink set Subject=@Subject,[" + Language + "Subject]=@Subject where GroupLinkId=@GroupLinkId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupLinkId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupLinkId"].Value = GroupLinkId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            try
            {
                sqlCommand.ExecuteNonQuery();

                sqlCommand.Parameters.Clear();
                sqlCommand.CommandText="Delete from TableGroupLinkItem where fk_GroupLink_Id=@GroupLinkId";
                sqlCommand.Parameters.Add("@GroupLinkId", SqlDbType.VarChar);
                sqlCommand.Parameters["@GroupLinkId"].Value = GroupLinkId;

                sqlCommand.ExecuteNonQuery();

                for (int iIndex = 0; iIndex < GroupLinkItems.Length; iIndex++)
                {
                    if (GroupLinkItems[iIndex].GroupLinkItemName != null)
                    {
                        if (GroupLinkItems[iIndex].GroupLinkItemName.Trim().Length > 0)
                        {
                            Query = "Insert into TableGroupLinkItem(GroupLinkItemId,GroupLinkItemName,[" + Language+ "GroupLinkItemName], Url,Target,IndexOrder,fk_GroupLink_Id,["+Language+"Description],belongSite) values (@GroupLinkItemId,@GroupLinkItemName,@GroupLinkItemName, @Url,@Target,@IndexOrder,@fk_GroupLink_Id,@Description,@belongSite)";
                            sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();
                            string GroupLinkItemId = GroupLinkItems[iIndex].GroupLinkItemId;
                            if (String.IsNullOrEmpty(GroupLinkItemId))
                            {
                                GroupLinkItemId = System.Guid.NewGuid().ToString();
                            }
                            sqlCommand.Parameters.Add("@GroupLinkItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@GroupLinkItemId"].Value = GroupLinkItemId;

                            sqlCommand.Parameters.Add("@GroupLinkItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@GroupLinkItemName"].Value = GroupLinkItems[iIndex].GroupLinkItemName;

                            sqlCommand.Parameters.Add("@Url", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Url"].Value = GroupLinkItems[iIndex].Url;

                            sqlCommand.Parameters.Add("@Target", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Target"].Value = GroupLinkItems[iIndex].Target;

                            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Description"].Value = GroupLinkItems[iIndex].Description;
                            

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = GroupLinkItems[iIndex].IndexOrder;

                            sqlCommand.Parameters.Add("@fk_GroupLink_Id", SqlDbType.VarChar);
                            sqlCommand.Parameters["@fk_GroupLink_Id"].Value = GroupLinkId;

                            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
                            sqlCommand.Parameters["@belongSite"].Value = GroupLinkCls.belongSite;

                            sqlCommand.ExecuteNonQuery();

                        }
                    }
                }
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }



        public void DeleteGroupLink(SqlTransaction sqlTransaction, string GroupLinkId)
        {
            GroupLinkCls oGroupLinkCls = new GroupLinkBLL().CreateModel(GroupLinkId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            Environment.Permission.DeletePermission(sqlTransaction, GroupLinkId);
            string Query =
                    " Delete from TableGroupLinkItem where fk_GroupLink_Id=@GroupLinkId;"+
                    " Delete from TableGroupLink where GroupLinkId=@GroupLinkId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@GroupLinkId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupLinkId"].Value = GroupLinkId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oGroupLinkCls != null)
                {
                    SubjectDeleted = oGroupLinkCls.Subject;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableGroupLink","Nhóm liên kết", GroupLinkId, SubjectDeleted);
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void SyncWithServer(
            string ConnectionString,
            string GroupLinkId,
            bool UseWsForDeploy,
            string ActiveLanguage,
            string PathRoot,
            string WorkingPath,
            string PortalAlias,
            string WsUrl,
            string WsSecurityCode)
        {
            if (UseWsForDeploy)
            {
                GroupLinkCls oGroupLinkCls = new GroupLinkBLL().CreateModel(ConnectionString,ActiveLanguage, GroupLinkId);
                DataSet ds = new DataSet();
                string Query =
                    " select * from TableGroupLink where GroupLinkId=@GroupLinkId;" +
                    " select * from TableGroupLinkItem where fk_GroupLink_Id=@GroupLinkId";
                SqlConnection sqlConnectionCheck = new SqlConnection(ConnectionString);
                sqlConnectionCheck.Open();
                SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                sqlCommandCheck.CommandText = Query;
                sqlCommandCheck.Parameters.Clear();
                sqlCommandCheck.Parameters.Add("@GroupLinkId", SqlDbType.VarChar);
                sqlCommandCheck.Parameters["@GroupLinkId"].Value = GroupLinkId;

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                sqlda.Fill(ds);

                ds.Tables[0].TableName = "TableGroupLink";
                ds.Tables[1].TableName = "TableGroupLinkItem";

                string XmlData = ds.GetXml();
                Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Environments.CreateAppWs(WsUrl).SyncData(
                    WsSecurityCode,
                    GroupLinkId,
                    oGroupLinkCls.belongSite,
                    XmlData,
                    null,
                    null,
                    null,
                    Framework.AppWs.ESycDataType.GroupLinks);
                if (oWsReturnParamCls.Error) throw new Exception("WS: " + oWsReturnParamCls.ErrorMsg);
            }
        }

        public void SyncWithServer(
            string GroupLinkId)
        {
            string WsUrl = Environments.GetConfigFromXml("AppWs");
            string WsSecurityCode = Portal.Environment.Environments.WsSecurityCode;
            bool UseWsForDeploy = Environments.UseWsForDeploy;
            SyncWithServer(
                Environment.Environments.ConnectionString,
                GroupLinkId,
                UseWsForDeploy,
                Environments.ActiveLanguage,
                Environments.PathRoot,
                Environments.WorkingPath,
                Portal.Environment.Environments.PortalAlias,
                WsUrl,
                WsSecurityCode);
        }
    }
}
